In [1]:
import os
import pandas
import numpy as np
from matplotlib import pyplot as plt
from matplotlib import ticker
import matplotlib.patches as mpatches
from IPython.display import display, HTML
import geopandas as gpd
import time
import io
from pprint import pprint
os.chdir('/home/idies/workspace/Temporary/raddick/cra_scratch/')
print(os.getcwd())
outdir = '/home/idies/workspace/Storage/raddick/Baltimore/community_reinvestment_act/'
print(outdir)
shapefiledir = '/home/idies/workspace/Storage/raddick/Baltimore/shapefiles/'
print(shapefiledir)
print('DONE')
/home/idies/miniconda3/lib/python3.6/importlib/_bootstrap.py:219: RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility. Expected 96, got 88
  return f(*args, **kwds)
/home/idies/miniconda3/lib/python3.6/importlib/_bootstrap.py:219: RuntimeWarning: numpy.dtype size changed, may indicate binary incompatibility. Expected 96, got 88
  return f(*args, **kwds)
/home/idies/workspace/Temporary/raddick/cra_scratch
/home/idies/workspace/Storage/raddick/Baltimore/community_reinvestment_act/
/home/idies/workspace/Storage/raddick/Baltimore/shapefiles/
DONE

Load shapefiles

In [2]:
s = time.time()
print('reading shapefile...')

tract_shapes_gdf = gpd.read_file(shapefiledir+'census_tracts_2010/geo_export_c50bbe56-543e-4878-9c9f-c56be327600a.shp', encoding='utf-8')
tract_shapes_gdf = tract_shapes_gdf.assign(tractname = pandas.to_numeric(tract_shapes_gdf['name'].apply(lambda x: x.split(' ')[-1]), errors='coerce'))
tract_shapes_gdf = tract_shapes_gdf.set_index('tractname')
e = time.time()
print('Read {0:,.0f} Maryland census tracts in {1:.3f} seconds.'.format(len(tract_shapes_gdf), e-s))

print('Calculating percent white...')
tract_shapes_gdf = tract_shapes_gdf.assign(percent_white = tract_shapes_gdf['white'] / tract_shapes_gdf['population'])

print('\nreading city boundary...')
boundary_gdf = gpd.read_file(shapefiledir+'baltimore_city_polygon/baltimore_city_polygon.shp')

print('\nreading water features...')
water_gdf = gpd.read_file(shapefiledir+'water/water.shp', encoding='utf-8')
water_gdf = water_gdf.set_index('OBJECTID')

s = time.time()
print('\nreading street centerlines...')
streets_gdf = gpd.read_file(shapefiledir+'streets/streetcl.shp', encoding='utf-8')
e = time.time()
print('Read {0:,.0f} streets in {1:.3f} seconds.'.format(len(streets_gdf), e-s))

s = time.time()
streets_gdf = streets_gdf.drop('OBJECTID_1', axis=1)
streets_gdf = streets_gdf.set_index('OBJECTID')
print('Cutting off streets at city boundary...')
s = time.time()
streets_gdf = gpd.sjoin(streets_gdf, boundary_gdf, op='within')
e = time.time()
print('Remaining: {0:,.0f} streets after {1:,.0f} seconds of processing.'.format(len(streets_gdf), e-s))
#streets_gdf = gpd.overlay(streets_gdf, boundary_gdf, how='intersection')

#print('\nSetting CRSes...')
#for x in (boundary_gdf, water_gdf, streets_gdf):
#    x.crs = tract_shapes_gdf.crs

print('DONE')
reading shapefile...
Read 200 Maryland census tracts in 0.106 seconds.
Calculating percent white...

reading city boundary...

reading water features...

reading street centerlines...
Read 48,160 streets in 9.734 seconds.
Cutting off streets at city boundary...
/home/idies/miniconda3/lib/python3.6/site-packages/geopandas/tools/sjoin.py:44: UserWarning: CRS of frames being joined does not match!
  warn('CRS of frames being joined does not match!')
Remaining: 47,642 streets after 14 seconds of processing.
DONE

Lending by geography of borrowers

Load all data

In [3]:
s = time.time()
baltimore_tracts_df = pandas.read_csv('tracts_processed.csv', encoding='utf-8', index_col='rownumber')
print('{0:,.0f} tract datas nationwide.'.format(len(baltimore_tracts_df)))
print('backing up...')
baltimore_tracts_df_bk = baltimore_tracts_df
e = time.time()
print('DONE in {0:,.0f} seconds.'.format(e-s))
/home/idies/miniconda3/lib/python3.6/site-packages/numpy/lib/arraysetops.py:518: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison
  mask |= (ar1 == a)
22,761,175 tract datas nationwide.
backing up...
DONE in 97 seconds.

Select Baltimore City, convert income tract data to low/moderate/medium/high

In [4]:
print('retrieving from backup...')
baltimore_tracts_df = baltimore_tracts_df_bk

baltimore_tracts_df = baltimore_tracts_df[(baltimore_tracts_df['state'] == 24) & (baltimore_tracts_df['county'] == 510)]
print('{0:,.0f} tract datas in Baltimore.'.format(len(baltimore_tracts_df)))

baltimore_tracts_df = baltimore_tracts_df[baltimore_tracts_df['loan_indicator'] == 'Y']
print('{0:,.0f} of them had loans.'.format(len(baltimore_tracts_df)))

print('Converting full level system to low/moderate/medium/upper...')
baltimore_tracts_df = baltimore_tracts_df.assign(cra_level = '')
baltimore_tracts_df.loc[(baltimore_tracts_df['income_group_total'].apply(lambda x: (x >= 1) & (x <=5))), 'cra_level'] = 'low'
baltimore_tracts_df.loc[(baltimore_tracts_df['income_group_total'].apply(lambda x: (x >= 6) & (x <=8))), 'cra_level'] = 'moderate'
baltimore_tracts_df.loc[(baltimore_tracts_df['income_group_total'].apply(lambda x: (x >= 9) & (x <=12))), 'cra_level'] = 'middle'
baltimore_tracts_df.loc[(baltimore_tracts_df['income_group_total'].apply(lambda x: (x == 13))), 'cra_level'] = 'upper'
baltimore_tracts_df.loc[(baltimore_tracts_df['income_group_total'].apply(lambda x: (x >= 14) & (x <=15))), 'cra_level'] = 'unknown'

print('backing up...')
baltimore_tracts_df_bk = baltimore_tracts_df
print('DONE')
retrieving from backup...
61,098 tract datas in Baltimore.
26,929 of them had loans.
Converting full level system to low/moderate/medium/upper...
backing up...
DONE

Aggregate all loans within census tracts

In [5]:
print('retrieving from backup...')
baltimore_tracts_df = baltimore_tracts_df_bk

print('grouping...')

tractdata = baltimore_tracts_df[baltimore_tracts_df['loan_indicator'] == 'Y'].groupby(['census_tract', 'activity_year', 'institution_name']).size()

tractdata = tractdata.sort_index()
# Run this to get every index value to display in every row:
#pandas.set_option('display.multi_sparse', False)

# GET ALL ROWS THAT MATCH A GIVEN CRITERION FROM A GIVEN LEVEL
#tractdata.xs(101.00, level=0) #Access all loans given in census tract 101
#tractdata.xs('low', level=1) # Access all loans given to low income group 
#tractdata.xs(2016, level=2) # Access all loans given in 2016
#tractdata.xs('WELLS FARGO BANK, N.A. (CA)', level=3) # Access all loans given by Wells Fargo

# FIND NUMBER OF LOANS FOR EACH VALUE AT EACH LEVEL

#tractdata.groupby([pandas.Grouper(level='census_tract')]).sum() # Number of loans given in each census tract
#tractdata.groupby([pandas.Grouper(level='cra_level')]).sum() # Number of loans given at each CRA income level
#tractdata.groupby([pandas.Grouper(level='activity_year')]).sum() # Number of loans given per year
#tractdata.groupby([pandas.Grouper(level='institution_name')]).sum() # Number of loans given per institution


# ITERATE THROUGH VALUES OF AN INDEX LEVEL AND SEE THE GROUPS FORMED BY THOSE VALUES
#grouped_by_census_tract = tractdata.groupby('census_tract')
#for name, group in grouped_by_census_tract:
#    print(name)
#    print(group)
#    print('\n')

# ACCESS A GROUP ASSOCIATED WITH A SPECIFIC VALUE ONE ONE LEVEL...
#grouped_by_census_tract = tractdata.groupby('census_tract')
#grouped_by_census_tract.get_group(101.00)
# OR ON MULTIPLE LEVELS...
#tractdata.groupby(['census_tract', 'institution_name']).get_group((101, '1ST MARINER BANK (MD)')) # note get_group takes a tuple

print('backing up...')
tractdata_bk = tractdata

print('OK')
#tractdata
retrieving from backup...
grouping...
backing up...
OK

Connect census tract aggregates with shapefiles

In [6]:
print('getting tract loan data from backup...')
tractdata = tractdata_bk

#tractdata
print('Grouping by tract only...')
grouped_by_census_tract = tractdata.groupby('census_tract').sum()

print('\nAdding loan data to shape data...')
tract_shapes_gdf = tract_shapes_gdf.assign(nLoans=grouped_by_census_tract)

#print('\nCalculating loans per 10,000 pepole...')
#tract_shapes_gdf = tract_shapes_gdf.assign(nLoansPer10k = (10000 * tract_shapes_gdf['nLoans']) / tract_shapes_gdf['population'])

print('matching up CSAs...')
tract_shapes_gdf = tract_shapes_gdf.to_crs(water_gdf.crs)

print('\nbacking up...')
tract_shapes_gdf_bk = tract_shapes_gdf

print('ok')
getting tract loan data from backup...
Grouping by tract only...

Adding loan data to shape data...
matching up CSAs...

backing up...
ok

Examine two census tracts: 1207 (Charles Village) and 1403 (Druid Heights)

In [8]:
scale = 1
fig, ax = plt.subplots(figsize=(48*scale,48*scale))
ax.set_aspect('equal')
ax.tick_params(axis='both', which='both', bottom=False, left=False, labelleft=False, labelbottom=False)

csa1 = tract_shapes_gdf[tract_shapes_gdf.index == 1207].plot(ax=ax, alpha=0.5, color='red', edgecolor='black', linewidth=2*scale)
csa2 = tract_shapes_gdf[tract_shapes_gdf.index == 1403].plot(ax=ax, alpha=0.5, color='green', edgecolor='black', linewidth=2*scale)

water_gdf[water_gdf['NAME'] == 'Harbor'].plot(ax=ax)
boundary_gdf.plot(ax=ax, color='none', edgecolor='black', linewidth=10*scale)
streets_gdf.plot(ax=ax, color='black', linewidth=0.5*scale)

#https://stackoverflow.com/questions/44098362/using-mpatches-patch-for-a-custom-legend
colors = ["r", "g"]
texts = ["Census tract 1207", "Census tract 1403"]
patches = [plt.plot([],[], marker="s", ms=100*scale, ls="", mec=None, color=colors[i], 
            label="{:s}".format(texts[i]) )[0]  for i in range(len(texts)) ]
plt.legend(handles=patches, bbox_to_anchor=(0.25, 0.15), 
           loc='center', ncol=1, numpoints=1, fontsize=80*scale, labelspacing=1*scale)
plt.title('Locator map for Baltimore census tracts', fontsize=84*scale)

plt.savefig(outdir+'figures/locator_1207_1403.svg', format='svg')
print('Figure saved')
#plt.show()
Figure saved

Number of loans by assessment area for those census tracts

Census tract 1207

In [9]:
loans_1207_by_assessment_area_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['census_tract'] == 1207].fillna('none or unknown').groupby('assessment_area_number').size(), 
)
#loans_1207_by_assessment_area_s.name = 'nLoans'
loans_1207_by_assessment_area_df = pandas.DataFrame(data=loans_1207_by_assessment_area_s.values, columns=['nLoans'], index=loans_1207_by_assessment_area_s.index)
loans_1207_by_assessment_area_df
Out[9]:
nLoans
assessment_area_number
1.0 22
2.0 1
5.0 2
6.0 1
7.0 2
8.0 1
9.0 2
13.0 3
18.0 1
19.0 6
25.0 1
26.0 1
30.0 1
47.0 1
51.0 11
53.0 1
71.0 4
73.0 2
74.0 2
78.0 1
84.0 1
109.0 1
112.0 1
129.0 2
131.0 2
173.0 1
176.0 1
177.0 1
180.0 1
4001.0 1
none or unknown 140

Census tract 1403

In [10]:
loans_1403_by_assessment_area_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['census_tract'] == 1403].fillna('none or unknown').groupby('assessment_area_number').size(), 
)
loans_1403_by_assessment_area_df = pandas.DataFrame(data=loans_1403_by_assessment_area_s.values, columns=['nLoans'], index=loans_1403_by_assessment_area_s.index)
loans_1403_by_assessment_area_df
Out[10]:
nLoans
assessment_area_number
1.0 5
6.0 2
9.0 1
13.0 2
19.0 2
30.0 1
51.0 4
57.0 1
71.0 3
73.0 2
84.0 1
110.0 1
112.0 1
131.0 1
173.0 1
174.0 1
176.0 1
177.0 1
180.0 1
1001.0 1
2401.0 1
4001.0 1
none or unknown 87

Number of loans by income group for those census tracts

Census tract 1207

In [11]:
loans_1207_by_income_group_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['census_tract'] == 1207].fillna('none or unknown').groupby('income_group').size(), 
)
loans_1207_by_income_group_df = pandas.DataFrame(data=loans_1207_by_income_group_s.values, columns=['nLoans'], index=loans_1207_by_income_group_s.index)
loans_1207_by_income_group_df
Out[11]:
nLoans
income_group
40% to 50% of MFI 134
50% to 60% of MFI 84

Census tract 1403

In [12]:
loans_1403_by_income_group_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['census_tract'] == 1403].fillna('none or unknown').groupby('income_group').size(), 
)
loans_1403_by_income_group_df = pandas.DataFrame(data=loans_1403_by_income_group_s.values, columns=['nLoans'], index=loans_1403_by_income_group_s.index)
loans_1403_by_income_group_df
Out[12]:
nLoans
income_group
40% to 50% of MFI 122

List of all loans in those census tracts

Census tract 1207

In [13]:
output = baltimore_tracts_df[[
    'census_tract', 'assessment_area_number', 'income_group', 'cra_level', 'institution_name'
]][baltimore_tracts_df['census_tract'] == 1207].fillna('').sort_values('income_group')

htmlString = '<table>'
htmlString += '<tr><th>Census tract</th><th>Assessment area</th><th>Income group</th><th>CRA level</th><th>Institution name</th></tr>'
for ix, thisrow in output.iterrows():
    htmlString += '<tr>'
    for thiscol in thisrow:
        htmlString += '<td>'+str(thiscol)+'</td>'
    htmlString += '<tr>'
htmlString += '</table>'

display(HTML(htmlString))
Census tractAssessment areaIncome groupCRA levelInstitution name
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1207.040% to 50% of MFIlowUnknown
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.019.040% to 50% of MFIlowNorthwest Bank (PA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowJPMorgan Chase Bank NA (OH)
1207.0129.040% to 50% of MFIlowPNC BANK N.A. (PA)
1207.01.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.019.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.030.040% to 50% of MFIlowPNC BANK N.A. (PA)
1207.0109.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.040% to 50% of MFIlowFIRST NATIONAL BANK OF OMAHA (NE)
1207.05.040% to 50% of MFIlowPNC BANK N.A. (PA)
1207.019.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowTD Bank N.A. (DE)
1207.071.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.0112.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.01.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1207.040% to 50% of MFIlowSandy Spring Bank (MD)
1207.040% to 50% of MFIlowUnknown
1207.0131.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.040% to 50% of MFIlowFIRST NATIONAL BANK OF OMAHA (NE)
1207.018.040% to 50% of MFIlowPNC BANK N.A. (PA)
1207.01.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.013.040% to 50% of MFIlowCapital One, N.A. (VA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowHSBC BANK, NA (VA)
1207.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1207.040% to 50% of MFIlowSynchrony Bank (UT)
1207.040% to 50% of MFIlowAmerican Express, FSB (UT)
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.074.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowHOWARD BANK (MD)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.073.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.01.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.047.040% to 50% of MFIlowPNC BANK N.A. (PA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.040% to 50% of MFIlowSandy Spring Bank (MD)
1207.074.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.01.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowUnknown
1207.019.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.013.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowWEX BANK (UT)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowAmerican Express Centurion Bk (UT)
1207.01.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.053.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowUnknown
1207.019.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.04001.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.05.040% to 50% of MFIlowUnknown
1207.051.040% to 50% of MFIlowM&T BANK (NY)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowChase Bank USA, NA (DE)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.019.040% to 50% of MFIlowBank of America, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.071.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1207.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.01.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.078.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1207.040% to 50% of MFIlowUnknown
1207.040% to 50% of MFIlowUnknown
1207.050% to 60% of MFImoderateTEXAS CAPITAL BANK (TX)
1207.071.050% to 60% of MFImoderatePNC BANK N.A. (PA)
1207.050% to 60% of MFImoderateCapital One Bank (USA), N.A. (VA)
1207.050% to 60% of MFImoderateChase Bank USA, NA (DE)
1207.050% to 60% of MFImoderateUnknown
1207.01.050% to 60% of MFImoderateBank of America, N.A. (CA)
1207.01.050% to 60% of MFImoderateCITIBANK, N.A. (NY)
1207.050% to 60% of MFImoderateUS BANK NA (WI)
1207.073.050% to 60% of MFImoderateSUNTRUST BANKS, INC (GA)
1207.050% to 60% of MFImoderateUnknown
1207.050% to 60% of MFImoderateCapital One Bank (USA), N.A. (VA)
1207.050% to 60% of MFImoderateWEX BANK (UT)
1207.08.050% to 60% of MFImoderateUnknown
1207.051.050% to 60% of MFImoderateM&T BANK (NY)
1207.050% to 60% of MFImoderateAmerican Express, FSB (UT)
1207.050% to 60% of MFImoderateWEX BANK (UT)
1207.050% to 60% of MFImoderateSynchrony Bank (UT)
1207.0176.050% to 60% of MFImoderateWELLS FARGO BANK, N.A. (CA)
1207.07.050% to 60% of MFImoderateNorthwest Bank (PA)
1207.050% to 60% of MFImoderateBanner Bank (WA)
1207.050% to 60% of MFImoderateAmerican Express, FSB (UT)
1207.050% to 60% of MFImoderateSynchrony Bank (UT)
1207.050% to 60% of MFImoderateChase Bank USA, NA (DE)
1207.050% to 60% of MFImoderateAmerican Express, FSB (UT)
1207.051.050% to 60% of MFImoderateM&T BANK (NY)
1207.050% to 60% of MFImoderateSandy Spring Bank (MD)
1207.050% to 60% of MFImoderateUnknown
1207.01.050% to 60% of MFImoderateThe Columbia Bank (MD)
1207.050% to 60% of MFImoderateWEX BANK (UT)
1207.09.050% to 60% of MFImoderateUnknown
1207.050% to 60% of MFImoderateHOWARD BANK (MD)
1207.050% to 60% of MFImoderateUS BANK NA (WI)
1207.084.050% to 60% of MFImoderatePNC BANK N.A. (PA)
1207.050% to 60% of MFImoderateCITIBANK, N.A. (NY)
1207.01.050% to 60% of MFImoderateBank of America, N.A. (CA)
1207.025.050% to 60% of MFImoderateCapital One, N.A. (VA)
1207.050% to 60% of MFImoderateTCF National Bank (SD)
1207.050% to 60% of MFImoderateCapital One Bank (USA), N.A. (VA)
1207.050% to 60% of MFImoderateSynchrony Bank (UT)
1207.0180.050% to 60% of MFImoderateWELLS FARGO BANK, N.A. (CA)
1207.050% to 60% of MFImoderateUnknown
1207.0177.050% to 60% of MFImoderateWELLS FARGO BANK, N.A. (CA)
1207.01.050% to 60% of MFImoderateCITIBANK, N.A. (NY)
1207.050% to 60% of MFImoderateALLY BANK (UT)
1207.06.050% to 60% of MFImoderateSUNTRUST BANKS, INC (GA)
1207.051.050% to 60% of MFImoderateM&T BANK (NY)
1207.050% to 60% of MFImoderateBofi Federal Bank (CA)
1207.050% to 60% of MFImoderateAmerican Express, FSB (UT)
1207.050% to 60% of MFImoderateSynchrony Bank (UT)
1207.050% to 60% of MFImoderateCapital One Bank (USA), N.A. (VA)
1207.050% to 60% of MFImoderateCitizens Bank, NA (RI)
1207.050% to 60% of MFImoderateTEXAS CAPITAL BANK (TX)
1207.050% to 60% of MFImoderateBarrington Bank and Trust (IL)
1207.050% to 60% of MFImoderateChase Bank USA, NA (DE)
1207.01.050% to 60% of MFImoderateBank of America, N.A. (CA)
1207.050% to 60% of MFImoderateCITIBANK, N.A. (NY)
1207.071.050% to 60% of MFImoderatePNC BANK N.A. (PA)
1207.01.050% to 60% of MFImoderateFIRST NATIONAL BANK OF PA (PA)
1207.050% to 60% of MFImoderateUS BANK NA (WI)
1207.050% to 60% of MFImoderateJPMorgan Chase Bank NA (OH)
1207.09.050% to 60% of MFImoderateUnknown
1207.01.050% to 60% of MFImoderateThe Columbia Bank (MD)
1207.050% to 60% of MFImoderateWEX BANK (UT)
1207.02.050% to 60% of MFImoderateHOWARD BANK (MD)
1207.0131.050% to 60% of MFImoderatePNC BANK N.A. (PA)
1207.050% to 60% of MFImoderateFIRST NATIONAL BANK OF OMAHA (NE)
1207.050% to 60% of MFImoderateUS BANK NA (WI)
1207.050% to 60% of MFImoderateJPMorgan Chase Bank NA (OH)
1207.0173.050% to 60% of MFImoderateWELLS FARGO BANK, N.A. (CA)
1207.050% to 60% of MFImoderateHOWARD BANK (MD)
1207.050% to 60% of MFImoderateAmerican Express, FSB (UT)
1207.050% to 60% of MFImoderateCapital One Bank (USA), N.A. (VA)
1207.01.050% to 60% of MFImoderateBank of America, N.A. (CA)
1207.050% to 60% of MFImoderateHSBC BANK, NA (VA)
1207.050% to 60% of MFImoderateChase Bank USA, NA (DE)
1207.050% to 60% of MFImoderateUnknown
1207.013.050% to 60% of MFImoderateCapital One, N.A. (VA)
1207.026.050% to 60% of MFImoderateBank of America, N.A. (CA)
1207.01.050% to 60% of MFImoderateCITIBANK, N.A. (NY)
1207.0129.050% to 60% of MFImoderatePNC BANK N.A. (PA)
1207.050% to 60% of MFImoderateJPMorgan Chase Bank NA (OH)
1207.07.050% to 60% of MFImoderateHOWARD BANK (MD)
1207.050% to 60% of MFImoderateUnknown
1207.050% to 60% of MFImoderateUnknown

Census tract 1403

In [14]:
output = baltimore_tracts_df[[
    'census_tract', 'assessment_area_number', 'income_group', 'cra_level', 'institution_name'
]][baltimore_tracts_df['census_tract'] == 1403].fillna('').sort_values('income_group')

htmlString = '<table>'
htmlString += '<tr><th>Census tract</th><th>Assessment area</th><th>Income group</th><th>CRA level</th><th>Institution name</th></tr>'
for ix, thisrow in output.iterrows():
    htmlString += '<tr>'
    for thiscol in thisrow:
        htmlString += '<td>'+str(thiscol)+'</td>'
    htmlString += '<tr>'
htmlString += '</table>'

display(HTML(htmlString))
Census tractAssessment areaIncome groupCRA levelInstitution name
1403.0177.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.013.040% to 50% of MFIlowCapital One, N.A. (VA)
1403.040% to 50% of MFIlowFIRST NATIONAL BANK OF OMAHA (NE)
1403.0174.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowAmerican Express, FSB (UT)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.0131.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.01.040% to 50% of MFIlow1ST MARINER BANK (MD)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.02401.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowChase Bank USA, NA (DE)
1403.040% to 50% of MFIlowUnknown
1403.0110.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.019.040% to 50% of MFIlowNorthwest Bank (PA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.06.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.051.040% to 50% of MFIlowM&T BANK (NY)
1403.040% to 50% of MFIlowAmerican Express, FSB (UT)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowTEXAS CAPITAL BANK (TX)
1403.040% to 50% of MFIlowCITIBANK, N.A. (NY)
1403.084.040% to 50% of MFIlowPNC BANK N.A. (PA)
1403.0180.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.057.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.051.040% to 50% of MFIlowM&T BANK (NY)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowTEXAS CAPITAL BANK (TX)
1403.040% to 50% of MFIlowUnknown
1403.01.040% to 50% of MFIlowBank of America, N.A. (CA)
1403.0176.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.073.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.051.040% to 50% of MFIlowM&T BANK (NY)
1403.040% to 50% of MFIlowAmerican Express, FSB (UT)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowFIRST NATIONAL BANK OF OMAHA (NE)
1403.0173.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowSandy Spring Bank (MD)
1403.040% to 50% of MFIlowAmerican Express, FSB (UT)
1403.073.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowChase Bank USA, NA (DE)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.071.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.051.040% to 50% of MFIlowM&T BANK (NY)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.013.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.01001.040% to 50% of MFIlow1ST MARINER BANK (MD)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.04001.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowAMERIS BANK (GA)
1403.040% to 50% of MFIlowLake Forest Bank and Trust (IL)
1403.06.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.040% to 50% of MFIlowAmerican Express, FSB (UT)
1403.040% to 50% of MFIlowSynchrony Bank (UT)
1403.040% to 50% of MFIlowMetaBank (IA)
1403.040% to 50% of MFIlowCapital One Bank (USA), N.A. (VA)
1403.040% to 50% of MFIlowBarrington Bank and Trust (IL)
1403.01.040% to 50% of MFIlowBank of America, N.A. (CA)
1403.040% to 50% of MFIlowCITIBANK, N.A. (NY)
1403.071.040% to 50% of MFIlowPNC BANK N.A. (PA)
1403.01.040% to 50% of MFIlowUnknown
1403.09.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.030.040% to 50% of MFIlowPNC BANK N.A. (PA)
1403.0112.040% to 50% of MFIlowWELLS FARGO BANK, N.A. (CA)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.019.040% to 50% of MFIlowNorthwest Bank (PA)
1403.01.040% to 50% of MFIlowUnknown
1403.071.040% to 50% of MFIlowSUNTRUST BANKS, INC (GA)
1403.040% to 50% of MFIlowSandy Spring Bank (MD)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowChase Bank USA, NA (DE)
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown
1403.040% to 50% of MFIlowUnknown

Analysis by CSA

Aggregate by CSA

In [15]:
print('retrieving tracts data from backup...')
baltimore_tracts_df = baltimore_tracts_df_bk

print('adding CSA data...')
census_tract_to_neighborhood_df = pandas.read_csv('/home/idies/workspace/Storage/raddick/persistent/cra/metadata/census_tract_to_neighborhood.csv', low_memory=False)#, index_col='NAME10')
census_tract_to_neighborhood_df = census_tract_to_neighborhood_df.set_index('NAME10')

baltimore_tracts_df = baltimore_tracts_df.join(census_tract_to_neighborhood_df, how='left', on='census_tract')

print('backing up...')
baltimore_tracts_df_bk = baltimore_tracts_df
print('\nok')

#census_tract_to_neighborhood_df#.dtypes

print('grouping by community statistical area...')

csaloans_df = baltimore_tracts_df[baltimore_tracts_df['loan_indicator'] == 'Y'].groupby(['CSA2010', 'cra_level', 'activity_year', 'institution_name']).size()

csaloans_df = csaloans_df.sort_index()
# Run this to get every index value to display in every row:
#pandas.set_option('display.multi_sparse', False)

# GET ALL ROWS THAT MATCH A GIVEN CRITERION FROM A GIVEN LEVEL
#tractdata.xs(101.00, level=0) #Access all loans given in census tract 101
#tractdata.xs('low', level=1) # Access all loans given to low income group 
#tractdata.xs(2016, level=2) # Access all loans given in 2016
#tractdata.xs('WELLS FARGO BANK, N.A. (CA)', level=3) # Access all loans given by Wells Fargo

# FIND NUMBER OF LOANS FOR EACH VALUE AT EACH LEVEL

#tractdata.groupby([pandas.Grouper(level='census_tract')]).sum() # Number of loans given in each census tract
#tractdata.groupby([pandas.Grouper(level='cra_level')]).sum() # Number of loans given at each CRA income level
#tractdata.groupby([pandas.Grouper(level='activity_year')]).sum() # Number of loans given per year
#tractdata.groupby([pandas.Grouper(level='institution_name')]).sum() # Number of loans given per institution


# ITERATE THROUGH VALUES OF AN INDEX LEVEL AND SEE THE GROUPS FORMED BY THOSE VALUES
#grouped_by_census_tract = tractdata.groupby('census_tract')
#for name, group in grouped_by_census_tract:
#    print(name)
#    print(group)
#    print('\n')

# ACCESS A GROUP ASSOCIATED WITH A SPECIFIC VALUE ONE ONE LEVEL...
#grouped_by_census_tract = tractdata.groupby('census_tract')
#grouped_by_census_tract.get_group(101.00)
# OR ON MULTIPLE LEVELS...
#tractdata.groupby(['census_tract', 'institution_name']).get_group((101, '1ST MARINER BANK (MD)')) # note get_group takes a tuple

print('\nbacking up...')
csaloans_df_bk = csaloans_df

print('grouping number of loans by CSA...')
nloans_by_csa = csaloans_df.groupby('CSA2010').sum()
nloans_by_csa.name = 'nLoans'

print('OK')
csaloans_df.sample(2)
retrieving tracts data from backup...
adding CSA data...
backing up...

ok
grouping by community statistical area...

backing up...
grouping number of loans by CSA...
OK
Out[15]:
CSA2010      cra_level  activity_year  institution_name        
Hamilton     moderate   2007           SUNTRUST BANKS, INC (GA)    1
Cherry Hill  low        2015           Chase Bank USA, NA (DE)     1
dtype: int64

Get population data for neighborhoods from their census tracts

In [16]:
print('Adding CSA names to tract geodata...')
census_tract_to_neighborhood_df = pandas.read_csv('/home/idies/workspace/Storage/raddick/persistent/cra/metadata/census_tract_to_neighborhood.csv', low_memory=False)
census_tract_to_neighborhood_df['NAME10'] = pandas.to_numeric(census_tract_to_neighborhood_df['NAME10'], errors='coerce')
census_tract_to_neighborhood_df = census_tract_to_neighborhood_df.set_index('NAME10')

tract_shapes_with_csa_gdf = tract_shapes_gdf.join(census_tract_to_neighborhood_df, how='left')

csa_gdf = tract_shapes_with_csa_gdf.groupby('CSA2010')[['white', 'population']].sum()

csa_gdf.sample(2)
Adding CSA names to tract geodata...
Out[16]:
white population
CSA2010
South Baltimore 5897.0 6406.0
Clifton-Berea 116.0 9874.0

Get CSA shapefiles

In [17]:
print('We have tract-based population data for {0:,.0f} CSAs, and CSA-based loan data for {1:,.0f}'.format(len(csa_gdf), len(nloans_by_csa)))

print('\nCombining census tract geometries into CSA geometries...')
csa_gdf = csa_gdf.assign(geometry='')
for thiscsa, thisrow in csa_gdf.iterrows():
    tracts_here = tract_shapes_with_csa_gdf[tract_shapes_with_csa_gdf['CSA2010'] == thiscsa].index.values.tolist()
    geos_here = tract_shapes_with_csa_gdf[tract_shapes_with_csa_gdf.index.isin(tracts_here)].geometry
    csa_gdf.loc[thiscsa, 'geometry'] = geos_here.unary_union
    
csa_gdf = csa_gdf.set_geometry('geometry')

print('\nAdding loan data...')
csa_gdf = csa_gdf.join(nloans_by_csa, how='left')

print('Calculating percent white...')
csa_gdf = csa_gdf.assign(percent_white = csa_gdf['white'] / csa_gdf['population'])

print('backing up...')
csa_gdf_bk = csa_gdf

csa_gdf.sample(2)
We have tract-based population data for 56 CSAs, and CSA-based loan data for 56

Combining census tract geometries into CSA geometries...

Adding loan data...
Calculating percent white...
backing up...
Out[17]:
white population geometry nLoans percent_white
CSA2010
Unassigned--Jail 809.0 5237.0 POLYGON ((1422260.725789612 595529.1030492543,... 4 0.154478
Beechfield/Ten Hills/West Hills 2112.0 12264.0 POLYGON ((1399042.70455329 588235.8089164203, ... 354 0.172211

Analyze loans per neighborhood business

In [18]:
# Get CSA data from backup
csa_gdf = csa_gdf_bk

business_data_dir = '/home/idies/workspace/Storage/raddick/Baltimore/community_reinvestment_act/mfi_and_business_data/'
businesses_by_csa_df = pandas.read_excel(business_data_dir+'Vital Signs Topic Area Workforce And Economic Development.xlsx')

businesses_by_csa_df = businesses_by_csa_df.rename(columns={'Community': 'CSA2010'})
businesses_by_csa_df = businesses_by_csa_df.set_index('CSA2010')
businesses_by_csa_df = businesses_by_csa_df.rename(columns={'2016 Data': 'businesses_per_1k_residents'})

csa_gdf = csa_gdf.join(businesses_by_csa_df)

csa_gdf = csa_gdf.assign(total_businesses = np.round(csa_gdf['businesses_per_1k_residents'] * csa_gdf['population'] / 1000,0))
csa_gdf = csa_gdf.assign(loans_per_business = csa_gdf['nLoans'] / csa_gdf['total_businesses'])

print('backing up...')
#csa_gdf_bk = csa_gdf
csa_gdf.head(1)
backing up...
Out[18]:
white population geometry nLoans percent_white businesses_per_1k_residents total_businesses loans_per_business
CSA2010
Allendale/Irvington/S. Hilton 1409.0 16217.0 POLYGON ((1404942.473027696 592901.001299308, ... 558 0.086884 8.3 135.0 4.133333

Examine two CSAs: GCVB and UDH

Locator map of Greater Charles Village/Barclay and Upton/Druid Heights

In [21]:
scale = 1#$0.25
fig, ax = plt.subplots(figsize=(48*scale,48*scale))
ax.set_aspect('equal')
ax.tick_params(axis='both', which='both', bottom=False, left=False, labelleft=False, labelbottom=False)

csa1 = csa_gdf[csa_gdf.index == 'Greater Charles Village/Barclay'].plot(ax=ax, alpha=0.5, color='red', edgecolor='black', linewidth=2*scale)
csa2 = csa_gdf[csa_gdf.index == 'Upton/Druid Heights'].plot(ax=ax, alpha=0.5, color='green', edgecolor='black', linewidth=2*scale)

#csa_gdf.plot(ax=ax, alpha=0.15, color='red', edgecolor='black', linewidth=8*scale)

water_gdf[water_gdf['NAME'] == 'Harbor'].plot(ax=ax)

boundary_gdf.plot(ax=ax, color='none', edgecolor='black', linewidth=10*scale)

streets_gdf.plot(ax=ax, color='black', linewidth=0.5*scale)

#for ix, thisrow in csa_gdf[csa_gdf['population'].notnull()].iterrows():
#    annotator = str(ix)
#    annotator = annotator.replace('/','/\n')
#    ax.annotate(annotator, 
#                xy=(thisrow.geometry.centroid.x, thisrow.geometry.centroid.y), 
#                xytext=(thisrow.geometry.centroid.x, thisrow.geometry.centroid.y), 
#                ha='center', va='center', backgroundcolor='white', fontsize=20*scale)

#https://stackoverflow.com/questions/44098362/using-mpatches-patch-for-a-custom-legend
colors = ["r", "g"]
texts = ["Greater Charles Village /\nBarclay", "Upton /\nDruid Heights"]
patches = [plt.plot([],[], marker="s", ms=100*scale, ls="", mec=None, color=colors[i], 
            label="{:s}".format(texts[i]) )[0]  for i in range(len(texts)) ]
plt.legend(handles=patches, bbox_to_anchor=(0.25, 0.15), 
           loc='center', ncol=1, numpoints=1, fontsize=64*scale, labelspacing=1*scale)
plt.title('Locator map for Baltimore community statistical areas', fontsize=80*scale)
    
#plt.show()
plt.savefig(outdir+'figures/locator_gcvb_dhu.svg', format='svg')
print('Figure saved!')
Figure saved!
In [22]:
print('{0:} has {1:,.0f} people and {2:,.0f} businesses.'.format('Greater Charles Village/Barclay', csa_gdf['population'].loc['Greater Charles Village/Barclay'], csa_gdf['total_businesses'].loc['Greater Charles Village/Barclay']))
print('{0:} received {1:,.0f} CRA loans, which is {2:.2f} per business.'.format('Greater Charles Village/Barclay', csa_gdf['nLoans'].loc['Greater Charles Village/Barclay'], csa_gdf['loans_per_business'].loc['Greater Charles Village/Barclay']))

loans_gcvb_by_income_group_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['CSA2010'] == 'Greater Charles Village/Barclay'].fillna('none or unknown').groupby('income_group').size(), 
)
loans_gcvb_by_income_group_df = pandas.DataFrame(data=loans_gcvb_by_income_group_s.values, columns=['nLoans'], index=loans_gcvb_by_income_group_s.index)
loans_gcvb_by_income_group_df
Greater Charles Village/Barclay has 16,391 people and 610 businesses.
Greater Charles Village/Barclay received 656 CRA loans, which is 1.08 per business.
Out[22]:
nLoans
income_group
30% to 40% of MFI 109
40% to 50% of MFI 96
50% to 60% of MFI 111
60% to 70% of MFI 170
70% to 80% of MFI 117
> 120% of MFI 53
In [23]:
print('{0:} has {1:,.0f} people and {2:,.0f} businesses.'.format('Upton / Druid Heights', csa_gdf['population'].loc['Upton/Druid Heights'], csa_gdf['total_businesses'].loc['Upton/Druid Heights']))
print('{0:} received {1:,.0f} CRA loans, which is {2:.2f} per business.'.format('Upton / Druid Heights', csa_gdf['nLoans'].loc['Upton/Druid Heights'], csa_gdf['loans_per_business'].loc['Upton/Druid Heights']))

loans_udh_by_income_group_s = pandas.Series(
    data=baltimore_tracts_df[baltimore_tracts_df['CSA2010'] == 'Upton/Druid Heights'].fillna('none or unknown').groupby('income_group').size(), 
)
loans_udh_by_income_group_df = pandas.DataFrame(data=loans_udh_by_income_group_s.values, columns=['nLoans'], index=loans_udh_by_income_group_s.index)
loans_udh_by_income_group_df
Upton / Druid Heights has 10,342 people and 144 businesses.
Upton / Druid Heights received 327 CRA loans, which is 2.27 per business.
Out[23]:
nLoans
income_group
10% to 20% of MFI 31
20% to 30% of MFI 119
30% to 40% of MFI 36
40% to 50% of MFI 122
< 10% of Median Family Income (MFI) 19